

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.Font;
import java.awt.Window;
import java.awt.event.*;
import java.io.File;
import java.io.FileOutputStream;
 
import java.sql.*;

import java.util.Locale;
import java.util.Vector;
import java.util.EventObject;
import javax.swing.*;
import javax.swing.table.*;
import javax.swing.event.*;
import javax.swing.filechooser.FileFilter;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;



class QueryTableModel extends AbstractTableModel {

	Vector cache;
	int colCount;
	String[] headers;
	Connection db;
	Statement statement;
	String currentURL;
	String drivername = "sun.jdbc.odbc.JdbcOdbcDriver";
	String colName;
	String s;
    int u_frheader_id ;
    
    
	public int getU_frheader_id() {
		return u_frheader_id;
	}

	public void setU_frheader_id(int u_frheader_id) {
		this.u_frheader_id = u_frheader_id;
	}

	public QueryTableModel() {
		cache = new Vector();

		try {
			Class.forName(drivername).newInstance();
		} catch (java.lang.Exception e) {
			System.err.println("Class not found exception : ");
			System.err.println(e.getMessage());
		}
	}//

	public String getColumnName(int i) {
		return headers[i];
	}

	public int getColumnCount() {
		return colCount;
	}

	public int getRowCount() {
		return cache.size();
	}

	public Object getValueAt(int row, int col) {
		return ((String[]) cache.elementAt(row))[col];
	}
/*	public void setValueAt(Object value,int row,int col)  {
		Object[] rowValue = (Object[]) cache.get(row);
		rowValue[col] = value;
		fireTableCellUpdated(row, col);// fr
	}
*/
	public void setValueAt(Object value, int row, int col) {// are you editing?
		/* prepare the query */
		/* you have to change the query to adapt it to your table */
		
		if (col == 4)
			colName = "thuyetminh";
		if (col == 2)
			colName = "so_dauky";
		if (col == 3)
			colName = "so_cuoiky";		
		if (((String[]) cache.elementAt(row))[col]==null) {
		 s = "update u_balancesheet set " + colName + " = '" + (String) value
				+ "' where " + colName + " is null " 
				+ " and u_bscode_id = (select u_bscode_id from u_bscode where code ='" + getValueAt(row,0) + "') and u_frheader_id = " + u_frheader_id ;
		 	System.out.println("value return if1 = " + s);
		} else 
		{
			 s = "update u_balancesheet set " + colName + " = '" + (String) value
			+ "' where " + colName + "='" 
			+ ((String[]) cache.elementAt(row))[col] + "'" +" and u_bscode_id = (select u_bscode_id from u_bscode where code ='" + getValueAt(row,0) + "') and u_frheader_id = " + u_frheader_id ;
			 System.out.println("value return if2 = " + s);
		}

		
		System.out.println("u_frheader_id = " + u_frheader_id);
		///System.out.println("row = "+ row);
		//System.out.println("value at col+1 = " + ((String[]) cache.elementAt(row))[col]   );
		String sCommit ="commit";
		
		//System.out.println("value before edit =" + getValueAt(row,col));
		
		//System.out.println("value before edit row2 = " + getValueAt(row,2));
		
		///System.out.println("CODE =" + getValueAt(row,0));
		/* excecute the query */
		try {
			statement.execute(s);
			statement.execute(sCommit);
		} catch (Exception e) {
			System.out.println("Could not updated");
		}

		((String[]) cache.elementAt(row))[col] = (String) value;
		fireTableCellUpdated(row, col);// also update the table
	}
	
	public boolean isCellEditable(int row, int col) {
		if (col==0 || col==1 ) {
		return false;
	} else
	{
		return true;
		}
	}

	public void setDriverName(String drivername) {
		this.drivername = drivername;

		try {
			Class.forName(drivername).newInstance();
		} catch (java.lang.Exception e) {
			System.err.println("Class not found exception : ");
			System.err.println(e.getMessage());
		}
	}

	public void setHostURL(String url) {
		if (url.equals(currentURL)) {
			return;
		}
		closeDB();
		initDB(url);
		currentURL = url;
	}

	public void setQuery(String q) {
		cache = new Vector();

		try {
			ResultSet rs = statement.executeQuery(q);
			ResultSetMetaData meta = rs.getMetaData();
			colCount = meta.getColumnCount();
			headers = new String[colCount];
			/*
			 * for (int h = 1; h <= colCount; h++) { headers[h - 1] =
			 * meta.getColumnName(h);
			 */
			for (int h = 1; h <= colCount; h++) {
				// headers[h - 1] = meta.getColumnName(h);
				if (meta.getColumnName(h).equals("name")) {
					headers[h - 1] = "Tên tài khoản";

				} else if (meta.getColumnName(h).equals("code")) {
					headers[h - 1] = "Số Tài khoản";
				} else if (meta.getColumnName(h).equals("so_dauky")) {
					headers[h - 1] = "Số Dư đầu kỳ";

				} else if (meta.getColumnName(h).equals("so_cuoiky")) {
					headers[h - 1] = "Số Dư cuối kỳ";
				} else if (meta.getColumnName(h).equals("thuyetminh")) {
					headers[h - 1] = "Thuyết Minh";
				} else {
					headers[h - 1] = meta.getColumnName(h);
				}
				System.out.println("Meta =" + meta.getColumnName(h));
				System.out.println("headers[h - 1] =" + headers[h - 1]);
			}
			while (rs.next()) {
				String[] record = new String[colCount];
				for (int i = 0; i < colCount; i++) {
					record[i] = rs.getString(i + 1);
				}
				cache.addElement(record);
			}

				fireTableChanged(null);
		} catch (Exception e) {
			cache = new Vector();
			e.printStackTrace();
		}
	}

	public void initDB(String url) {
		try {
			db = DriverManager.getConnection(url);
			statement = db.createStatement();
		} catch (Exception e) {
			System.out.println("Database could not started");
			e.printStackTrace();
		}
	}

	public void closeDB() {
		try {
			if (statement != null) {
				statement.close();
			}
			if (db != null) {
				db.close();
			}
		} catch (Exception e) {
			System.out.println("Database could not closed");
			e.printStackTrace();
		}
	}

}

public class TableDeneme extends JFrame {

	QueryTableModel qtm;
	JTable table;
	JScrollPane scrollpane;
	JPanel p1, p2;
	JButton jb, jxl;
	ListSelectionModel rowSM;
	Font f1 = new Font("Arial", Font.BOLD, 14);

	JLabel lblid, lblname;
	JTextField txtid;

	String gen;
	ResultSet rs = null;
	Connection con = null;
	Statement stmt = null;
	// AN
	// private NumberFormat numberFormat= new SimpleNumberFormat("###.###.###,##");

	public TableDeneme() {
		super("Báo cáo tài chính của Doanh Nghiệp");
		qtm = new QueryTableModel();
		table = new JTable(qtm);
		scrollpane = new JScrollPane(table);
		p1 = new JPanel();
		jb = new JButton("Load Adempiere");

		jxl = new JButton("Export Excel");

		p1.add(jb);
			
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		getContentPane().add(p1, BorderLayout.NORTH);
		getContentPane().add(scrollpane, BorderLayout.CENTER);
		setExtendedState(JFrame.MAXIMIZED_BOTH);
		
		//addWindowListener(new BasicWindowMonitor());
		
		setSize(1200, 900);
		setVisible(true);
		jb.setFont(f1);

		lblid = new JLabel("u_fheader_id = ");
		txtid = new JTextField(15);

		lblname = new JLabel("Tên Doanh Nghiệp = ");

		lblid.setBounds(300, 140, 100, 20);
		lblname.setBounds(300, 180, 100, 20);

		txtid.setBounds(400, 140, 100, 20);

		// p1.add(lblid);

		txtid.setText("2293765");  //134325   //133748
		qtm.setU_frheader_id(new Integer(txtid.getText()).intValue());
		
		jxl.setEnabled(false);
		p1.add(jxl);
		p1.add(txtid);
		p1.add(lblname);

		jxl.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				// txtid.setText("Vănvq");
				
				JFileChooser chooser = new JFileChooser();
				chooser.setFileFilter(new FileFilter() {

					@Override
					public boolean accept(File file) {
						if (file.isDirectory()) {
							return true;
						}
						if (file.isFile() && file.getName().endsWith(".xls")) {
							return true;
						}
						return false;
					}

					@Override
					public String getDescription() {
						return "Excel (*.xls)";
					}
				});
				chooser.setDialogTitle("Save excel file to...");

				int state = chooser.showSaveDialog(null);
				File file = chooser.getSelectedFile();

				if (file != null && state == JFileChooser.APPROVE_OPTION) {
					String fileName = file.getAbsolutePath();
					if (!fileName.endsWith(".xls")) {
						fileName += ".xls";
						file = new File(fileName);
					}

					try {
			 

						HSSFWorkbook workbook = new HSSFWorkbook();
						
						
				        HSSFFont boldFont = workbook.createFont();
				        boldFont.setColor(HSSFColor.BLUE.index);
				        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				        boldFont.setFontHeightInPoints((short)11);


				        

				        HSSFCellStyle headerStyle = workbook.createCellStyle();
				        headerStyle.setFont(boldFont);
				        headerStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.index);
						
				        HSSFFont boldFontName = workbook.createFont();
				        //boldFontName.setColor(HSSFColor.index);
				        boldFontName.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				        boldFontName.setFontHeightInPoints((short)15);

				        HSSFCellStyle nameStyle = workbook.createCellStyle();
				        nameStyle.setFont(boldFontName);
				        nameStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.index);
				        
				        
						//HSSFCellStyle headerStyle = workbook.createCellStyle();
						//headerStyle.setFillForegroundColor(new HSSFColor.BLUE()
						//		.getIndex());
						//headerStyle.setFillForegroundColor(new HSSFColor.RED().getIndex());

						HSSFSheet firstSheet = workbook
								.createSheet("Sheet 1");
						HSSFCell cellA = null;
						HSSFCell cellB = null;
						HSSFCell cellC = null;
						HSSFCell cellD = null;
						
						
						
						Object cellValue = null;
						
						int row = 0;
						
						HSSFRow rowB = firstSheet.createRow(row++);

						
						
						cellB = rowB.createCell(1);						
						cellB.setCellValue("Bản báo cáo tài chính của : " + lblname.getText());
						cellB.setCellStyle(nameStyle);
						

						HSSFRow rowC = firstSheet.createRow(row++);						
						cellC = rowC.createCell(2);
						HSSFRow rowD = firstSheet.createRow(row++);						
						cellD = rowD.createCell(2);
						
						
						HSSFRow rowA = firstSheet.createRow(row++);
						
						// Fist Row
						for (int i = 0; i < table.getColumnCount(); i++) {
							cellA = rowA.createCell(i);
							String columnName = table.getColumnName(i);
							cellA.setCellValue(new HSSFRichTextString(columnName));
							cellA.setCellStyle(headerStyle);
						}
						

						
						HSSFFont cellFont = workbook.createFont();
						//cellFont.setColor(HSSFColor.RED.index);
						
						//cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
						cellFont.setFontHeightInPoints((short)11);


				        HSSFCellStyle cellStyle = workbook.createCellStyle();
				        cellStyle.setFont(cellFont);
				        cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
				        
				        
				        
				        //
				        
				        DecimalFormat format= new DecimalFormat("###,###.##");
						format.setDecimalFormatSymbols(new DecimalFormatSymbols(Locale.GERMAN));
						
						//
						for (int i = 0; i < table.getRowCount(); i++) {
							rowA = firstSheet.createRow(row++);
							for (int j = 0; j < table.getColumnCount(); j++) {
								HSSFCell cell = rowA.createCell(j);
								Object obj = table.getValueAt(i, j);
								String value = obj == null ? "" : obj
										.toString();
								System.out.println("Value =" + value);
								if (j==1) {
									
									
								}
								firstSheet.autoSizeColumn(j);
								// SO DU DAU KY CUOI KY (NUMERIC VALUE)
								if (j == 2 || j == 3) {
									try {
										Number num =format.parse(value);
										//String value2 = value.replace(",", "");
										//Double d = Double.parseDouble(value2);
										Double d= num.doubleValue();
										cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
									   //new org.apache.poi.hssf.usermodel.HSSF
										if( 130 < d && d < 150)  {
											// cell.setCellStyle(cellStyle);
										}else {
											cell.setCellStyle(cellStyle);
											
										}										
										cell.setCellValue( d );
									} catch (Exception e1) {
										e1.printStackTrace();
									}
								} else {// CHARACTOR VALUE

									
									cell.setCellType(HSSFCell.CELL_TYPE_STRING);
									//cellStyle.setAlignment((short)cellStyle.ALIGN_CENTER);
									cell.setCellValue(new HSSFRichTextString(value));
								}
								
									
								
							}
						}
						
						FileOutputStream out = new FileOutputStream(file);
						workbook.write(out);
						out.close();

					} catch (Exception ex) {
						JOptionPane.showMessageDialog(null, "Export bị lỗi...");
						ex.printStackTrace();
					}
				}

			}

		});

		jb.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				// qtm.setHostURL("jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=281;DBQ=db1.mdb");
				// Access DB
				// qtm.setDriverName("sun.jdbc.odbc.JdbcOdbcDriver");
				// qtm.setHostURL("jdbc:odbc:mydatasource;UID=admin");
				// qtm.setQuery("select * from soz");

				// Posgest !!!!
				qtm.setU_frheader_id(new Integer(txtid.getText()));
				if (e.getActionCommand() == "Load Adempiere")
					try {
						qtm.setDriverName("org.postgresql.Driver");
						qtm.setHostURL("jdbc:postgresql://172.16.0.2:5432/adempiere?user=adempiere&password=adempiere");
						qtm.setQuery("select bs.code,bs.name, bl.so_dauky,bl.so_cuoiky,bl.thuyetminh  from u_bscode bs, u_balancesheet bl where bs.u_bscode_id=bl.u_bscode_id and bl.u_frheader_id="
								+ "2293765" + " order by bs.code");
						// qtm.setQuery("select id,code,name from cpms_slc_unicode");
						// getData();
						
						 

						table.getColumnModel()
								.getColumn(2)
								.setCellRenderer(
										new DecimalFormatRenderer(
												"###,###,###.00"));
						table.getColumnModel()
								.getColumn(3)
								.setCellRenderer(
										new DecimalFormatRenderer(
												"###,###,###.00"));

						// table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

						// table.getColumnModel().getColumn(0).setMaxWidth(7700);
						table.getColumnModel().getColumn(0).setMinWidth(100);
						table.getColumnModel().getColumn(1).setMinWidth(400);
						table.getColumnModel().getColumn(2).setMinWidth(280);
						table.getColumnModel().getColumn(3).setMinWidth(280);
						table.getColumnModel().getColumn(4).setMinWidth(120);

						alignRight(table, 0, "C");
						alignRight(table, 2, "R");
						alignRight(table, 3, "R");
						alignRight(table, 4, "C");

						
						if (table.getColumnCount() >0) {
							getData();
							jxl.setEnabled(true);
						} else
						{
							jxl.setEnabled(false);
							lblname.setText("");
						}
						
					} catch (Exception ex) {
						ex.printStackTrace();

					}

			}

			private void alignRight(JTable table, int column, String s) {

				// DefaultTableCellRenderer rightRenderer = newname	TableDeneme.java
				// DefaultTableCellRenderer();
				// DefaultTableCellRenderer rightRenderer =
				// (DefaultTableCellRenderer) table
				// .getCellRenderer(0, column);

				DefaultTableCellRenderer rightRenderer = (DefaultTableCellRenderer) table
						.getColumnModel().getColumn(column).getCellRenderer();
				if (rightRenderer == null) {
					rightRenderer = new DefaultTableCellRenderer();
				}
				if (s == null) {
					return;
				}
				// System.out.println("SS=" + s);
				if (s.equals("R")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.RIGHT);
				}
				if (s.equals("L")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.LEFT);
				}
				if (s.equals("C")) {
					rightRenderer.setHorizontalAlignment(SwingConstants.CENTER);
				}// */
				table.getColumnModel().getColumn(column)
						.setCellRenderer(rightRenderer);

			}
		});

	}
	class DecimalFormatRenderer extends DefaultTableCellRenderer {
		// NumberFormat format = null;
		// Locale loc = new Locale("ge", "GE");
		// NumberFormat currFmt = new NumberFormat
		DecimalFormat format = null;

		// double amt = 123456.78;
		// System.out.println(currFmt.format(amt));

		public DecimalFormatRenderer(String formatString) {
			// this.formatString = formatString;
			format = new DecimalFormat(formatString);
			format.setDecimalFormatSymbols(new DecimalFormatSymbols(Locale.GERMAN));
		}

		@Override
		public Component getTableCellRendererComponent(JTable table,
				Object value, boolean isSelected, boolean hasFocus, int row,
				int column) {

			JLabel label = (JLabel) super.getTableCellRendererComponent(table,
					value, isSelected, hasFocus, row, column);

			if (value instanceof java.lang.Number) {
				Number newValue = (Number) value;
				String text = format.format(newValue.doubleValue());
				label.setText(text);
			} else if (value instanceof String) {
				// System.out.println("String");

				Number newValue = null;
				try {

					newValue = Double.parseDouble((String) value);

					String text = format.format(newValue.doubleValue());
					label.setText(text);
					

					if ( row%2  == 0) {

						//setBackground(Color.lightGray);

					} else {
						setBackground(Color.white);
						setForeground(Color.black);
					}

				} catch (Exception e) {
				}

			}

			return label;

		}

	}	
//Lay ten cty
	public void getData() {
		try {
			
			//lblname.setText("vanvq");
			qtm.setU_frheader_id(new Integer(txtid.getText()));
			
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			
			con = DriverManager
					.getConnection("jdbc:postgresql://localhost/adempiere?user=adempiere&password=adempiere");
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			rs = stmt
					.executeQuery("select name from c_bpartner where c_bpartner_id=(select c_bpartner_id from u_frheader where u_frheader_id=" + qtm.getU_frheader_id() + ")");
			
			rs.first();
			System.out.println(rs.getString(1));
			lblname.setText(rs.getString(1));
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		TableDeneme td = new TableDeneme();

	}
}
